package cn.explink.dop.dao;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;

import cn.explink.dop.entity.vo.DeliveryManVO;
import cn.explink.dop.enums.DeliveryManEnum;
import cn.explink.eap.core.common.dao.impl.GenericBaseCommonDao;

/**
 * 业务人员管理dao层
 *
 * @author firefly 2015-2-28 10:40
 *
 */
@Repository("deliveryManDao")
public class DeliveryManDao extends GenericBaseCommonDao<DeliveryManVO, String> {
	
	public List<DeliveryManVO> getByStatus(Integer status) {
		StringBuffer sql = new StringBuffer("from DeliveryManVO ");
		TreeSet<Integer> set = new TreeSet<Integer>();
		set.add(status);
		sql.append(" ").append("where status in (").append(StringUtils.join(set.toArray(), ",")).append(")");

		@SuppressWarnings("unchecked")
		List<DeliveryManVO> deliveryManVOList = this.getSession().createQuery((sql.toString())).list();
		return deliveryManVOList;
	}
	
	/**
	 * 
	* @Title: queryDeliveryManByCompanyId 
	* @Description: 通过公司id查询该公司内部所有的配送员
	* @author 小强
	* @param @param id
	* @param @return    设定文件 
	* @return List<String>    返回类型 
	* @throws
	 */
	public List<String> queryDeliveryManByCompanyId(String id){
		List<String> list = new ArrayList<String>();
		StringBuffer sql = new StringBuffer();
		sql.append("select id from bd_deliveryman where Company_Id='" + id+"'");
		Query query = this.getSession().createSQLQuery(sql.toString());
		list = query.list();
		return list;
	}
	
	/**
	 * 
	* @Title: updateStatusOnly 
	* @Description: 修改配送员status字段 
	* @author 小强
	* @param @param ids
	* @param @param status    设定文件 
	* @return void    返回类型 
	* @throws
	 */
	public void updateStatusOnly(final List<String> ids, int status) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("update bd_deliveryman set status=" + status);
		
		sql.append(" where id=?");
		this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, ids.get(i));
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return ids.size();
			}
		});
	}
	
	/**
	 * 修改基本信息
	 *
	 * @param baseInfo
	 *            基本信息对象
	 */
	public void modify(final DeliveryManVO del) {
		StringBuffer sql = new StringBuffer("update bd_deliveryman set del_telephone=? ");
		sql.append(" ,del_introduction=? ,del_certificates=?,del_portrait=? where id=? ");
		this.getJdbcTemplate().update(sql.toString(), del.getDelTelephone(), del.getDelIntroduction(), del.getDelCertificates(), del.getDelPortrait(), del.getId());
	}

	/**
	 * 按照实体查询，无分页
	 *
	 * @param res
	 *            查询实体
	 * @return 查询列表
	 */
	public List<DeliveryManVO> finds(final DeliveryManVO del, final List<String> list) {
		StringBuffer selectSql = new StringBuffer(" from DeliveryManVO ");
		StringBuffer whereSql = this.getWhereSql(del,list);

		@SuppressWarnings("unchecked")
		List<DeliveryManVO> deliveryManVOList = this.getSession().createQuery(selectSql.append(whereSql).toString()).list();
		return deliveryManVOList;
	}

	public DeliveryManVO queryById(String deliveryManId) {
		Query query = this.getSession().createQuery(" from DeliveryManVO where id= :deliveryManId");
		query.setString("deliveryManId", deliveryManId);
		DeliveryManVO deliveryManVO=(DeliveryManVO)query.uniqueResult();
		return deliveryManVO;
	}

	public DeliveryManVO queryByUserId(String Id) {
		Query query = this.getSession().createQuery(" from DeliveryManVO man  where man.sUser.id= :Id");
		query.setString("Id", Id);
		return (DeliveryManVO) query.uniqueResult();
	}

	private StringBuffer getWhereSql(final DeliveryManVO del,final List<String> list) {
		StringBuffer sql = new StringBuffer();
		sql.append(" where del_enabled=").append(DeliveryManEnum.Enable.getValue());		
		sql.append(" and Company_Id in('"+StringUtils.join(list,"','")).append("')");
		if (StringUtils.isNotBlank(del.getDelName())) {
			sql.append(" and del_name='").append(del.getDelName()).append("'");
		}
		if (StringUtils.isNotBlank(del.getDelCertificates())) {
			sql.append(" and del_certificates='").append(del.getDelCertificates()).append("'");
		}
		if (StringUtils.isNotBlank(del.getDelPortrait())) {
			sql.append(" and del_portrait='").append(del.getDelPortrait()).append("'");
		}
		if (StringUtils.isNotBlank(del.getDelTelephone())) {
			sql.append(" and del_teliPhone>='").append(del.getDelTelephone()).append("'");
		}
		if (StringUtils.isNotBlank(del.getDelIntroduction())) {
			sql.append(" and del_introduction='").append(del.getDelIntroduction()).append("'");
		}
		if (0 != del.getDelMaxmum()) {
			sql.append(" and del_maxmum=").append(del.getDelMaxmum());
		}
		if ((del.getsUser() != null) && StringUtils.isNotBlank(del.getsUser().getId())) {
			sql.append(" and s_user='").append(del.getsUser()).append("'");
		}
		if (del.getIsDeliveryMan() != 0) {
			sql.append(" and is_DeliveryMan=" + del.getIsDeliveryMan());
		}
		/*
		 * if ((del.getHavingPOS() != -1)) {
		 * sql.append(" and having_pos=").append(del.getHavingPOS()); } if
		 * ((del.getHavingWarmBox() != -1)) {
		 * sql.append(" and having_warm_box=").append(del.getHavingWarmBox()); }
		 */
		return sql;
	}

	/**
	 * 按照实体获取分页数据
	 *
	 * @param baseInfo
	 *            查询实体
	 * @param pageNum
	 *            当前页
	 * @param pageSize
	 *            当前页记录条数
	 * @return 查询结果集
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> findByPage(final DeliveryManVO del, final int pageNum, final int pageSize) {
		StringBuffer allFromSql = new StringBuffer(" from DeliveryManVO man where 1=1 ");
		StringBuffer countFromSql = new StringBuffer("select count(id) from DeliveryManVO man where 1=1 ");
		StringBuffer whereSql = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		whereSql.append(" and del_enabled<>").append(DeliveryManEnum.DELETE.getValue());
		if (StringUtils.isNotBlank(del.getDelName())) {
			whereSql.append(" and man.delName like ? ");
			params.add("%" + del.getDelName() + "%");
		}
		if (StringUtils.isNotBlank(del.getDelCertificates())) {
			whereSql.append(" and del_certificates like ? ");
			params.add("%" + del.getDelCertificates() + "%");
		}
		if (StringUtils.isNotBlank(del.getDelTelephone())) {
			whereSql.append(" and del_telephone like ? ");
			params.add("%" + del.getDelTelephone() + "%");
		}
		if (0 != del.getDelMaxmum()) {
			whereSql.append(" and del_maxmum=").append(del.getDelMaxmum());
		}

		Map<String, Object> map = new HashMap<String, Object>();
		Query queryCount = this.getSession().createQuery(countFromSql.append(whereSql).toString());
		for (int i = 0; i < params.size(); i++) {
			queryCount.setParameter(i, params.get(i));
		}
		Integer count = ((Long) queryCount.uniqueResult()).intValue();

		map.put("count", count);

		String sql = allFromSql.append(whereSql).toString();

		Query queryPage = this.getSession().createQuery(sql.toString());
		for (int i = 0; i < params.size(); i++) {
			queryPage.setParameter(i, params.get(i));
		}
		queryPage.setFirstResult((pageNum - 1) * pageSize);
		queryPage.setMaxResults(pageSize);
		List<DeliveryManVO> deliveryManList = queryPage.list();
		map.put("list", deliveryManList);
		return map;
	}

	/**
	 * 删除选中对象
	 *
	 * @param baseInfoList
	 *            参数集合
	 */
	public int delete(final List<DeliveryManVO> resList) {
		StringBuffer sql = new StringBuffer("delete from bd_deliveryman");
		TreeSet<String> set = new TreeSet<String>();
		for (DeliveryManVO d : resList) {
			set.add("'" + d.getId() + "'");
		}
		sql.append(" ").append("where id in (").append(StringUtils.join(set.toArray(), ",")).append(")");
		return super.getJdbcTemplate().update(sql.toString());
	}

	/**
	 * 获取用户信息
	 *
	 * @return 用户信息列表
	 */
	public List<Map<String, Object>> getUserList() {
		return this.getJdbcTemplate().queryForList(
				"select ID, realname from t_s_base_user where status = 1 and departid='4028800f4c354c65014c359421130002' and id not in(select man.s_user from bd_deliveryman man )");
	}

	/**
	 *
	 * @Title: checkUserRelevance
	 * @Description: 查询当前用户是否已经关联了派送人员
	 * @author 孟凡岭
	 * @param @param userId
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> checkUserRelevance(String userId) {
		// TODO Auto-generated method stub
		String sql = "select s_user from  bd_deliveryman where s_user=? and del_enabled=1";

		return this.getJdbcTemplate().queryForList(sql, userId);
	}

	/**
	 *
	 * @Title: chekcDelCertificates
	 * @Description: 按证件编号查询数据
	 * @author 孟凡岭
	 * @param @param delCertificates
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> chekcDelCertificates(String delCertificates) {
		// TODO Auto-generated method stub
		String sql = "select del_certificates from bd_deliveryman where del_certificates=?";
		return this.getJdbcTemplate().queryForList(sql, delCertificates);
	}

	/**
	 *
	 * @Title: open
	 * @Description: 启用人员
	 * @author 孟凡岭
	 * @param @param ids
	 * @return void 返回类型
	 * @throws
	 */
	public void updateStatus(final List<String> ids, int status) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer("update bd_deliveryman set del_enabled=" + status);
		/**
		 * 如果状态为停用，那么也将接单状态改为不可接单
		 */
		if (status == 2) {
			sql.append(" ,status=2");
		}
		sql.append(" where id=?");
		this.getJdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, ids.get(i));
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return ids.size();
			}
		});
	}

	/**
	 *
	 * @Title: getUsersAndSelf
	 * @Description: 根据用户ID来查询信息
	 * @author 孟凡岭
	 * @param @param userId
	 * @param @return
	 * @return Map<String,Object> 返回类型
	 * @throws
	 */
	public Map<String, Object> getUsersAndSelf(String userId) {
		// TODO Auto-generated method stub
		String sql = "select ID, realname from t_s_base_user where id=? ";
		return this.getJdbcTemplate().queryForMap(sql, userId);
	}

	/**
	 *
	 * @Title: updateUser
	 * @Description: 更新用户状态
	 * @author 孟凡岭
	 * @param @param ids
	 * @param @return
	 * @return void 返回类型
	 * @throws
	 */
	public void updateUser(List<String> ids, int status) {
		// TODO Auto-generated method stub
		List<String> list = new ArrayList<String>();
		for (int i = 0; i < ids.size(); i++) {
			list.add("'" + ids.get(i) + "'");
		}
		String id = StringUtils.join(list.toArray(), ",");
		String sql = "select s_user from bd_deliveryman where id in (" + id + ")";
		final List<String> userIds = this.getJdbcTemplate().queryForList(sql, String.class);
		sql = "update t_s_base_user set status=" + status + " where id=? ";
		this.getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// TODO Auto-generated method stub
				ps.setString(1, userIds.get(i));
			}

			@Override
			public int getBatchSize() {
				// TODO Auto-generated method stub
				return userIds.size();
			}
		});
	}

	/**
	 *
	 * @Title: getOpenDeliveryMan
	 * @Description: 获取启用中的配送人员
	 * @author 孟凡岭
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> getOpenDeliveryMan() {
		// TODO Auto-generated method stub
		String sql = "select man.id as id,man.del_name as name  from bd_deliveryman man where man.del_enabled=1";
		return this.getJdbcTemplate().queryForList(sql);
	}

	/**
	 *
	 * @Title: deleteByUserId
	 * @Description: 删除配送员，逻辑删除
	 * @author 孟凡岭
	 * @param @param id
	 * @return void 返回类型
	 * @throws
	 */
	public void deleteByUserId(String id) {
		// TODO Auto-generated method stub
		String sql = "update bd_deliveryman set del_enabled=" + DeliveryManEnum.DELETE.getValue() + " where s_user=?";
		this.getJdbcTemplate().update(sql, id);
	}

	/**
	 *
	 * @Title: getOpenAndReceiveDeliveryMan
	 * @Description: 获取启用中并且可以接单的配送人员
	 * @author 孟凡岭
	 * @param @return
	 * @return List<Map<String,Object>> 返回类型
	 * @throws
	 */
	public List<Map<String, Object>> getOpenAndReceiveDeliveryMan() {
		String sql = "select man.id as id,man.del_name as name  from bd_deliveryman man where man.del_enabled=1 and status=1 ";
		return this.getJdbcTemplate().queryForList(sql);
	}

	/**
	 *
	 * @Title: queryManListPageByCompanyId
	 * @Description: 通过公司号查询配送员信息
	 * @author 孟凡岭
	 * @param @param man
	 * @param @param companyId
	 * @param @param pageNum
	 * @param @param pageSize
	 * @param @return
	 * @return Map<String,Object> 返回类型
	 * @throws
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> queryManListPageByCompanyId(DeliveryManVO man, String companyId, int pageNum, int pageSize) {
		StringBuffer allFromSql = new StringBuffer(" from DeliveryManVO man where 1=1 ");
		StringBuffer countFromSql = new StringBuffer("select count(*) from DeliveryManVO where 1=1 ");
		StringBuffer whereSql = new StringBuffer();
		List<Object> params = new ArrayList<Object>();
		whereSql.append(" and del_enabled<>").append(DeliveryManEnum.DELETE.getValue());
		if (StringUtils.isNotBlank(man.getDelName())) {
			whereSql.append(" and del_name like ? ");
			params.add("%" + man.getDelName() + "%");
		}
		if (StringUtils.isNotBlank(man.getDelCertificates())) {
			whereSql.append(" and del_certificates like ? ");
			params.add("%" + man.getDelCertificates() + "%");
		}
		if (StringUtils.isNotBlank(man.getDelTelephone())) {
			whereSql.append(" and del_telephone like ? ");
			params.add("%" + man.getDelTelephone() + "%");
		}
		whereSql.append(" and Company_Id=? ");
		params.add(companyId);
		Map<String, Object> map = new HashMap<String, Object>();
		Query queryCount = this.getSession().createQuery(countFromSql.append(whereSql).toString());
		for (int i = 0; i < params.size(); i++) {
			queryCount.setParameter(i, params.get(i));
		}
		Integer count = ((Long) queryCount.uniqueResult()).intValue();

		map.put("count", count);

		String sql = allFromSql.append(whereSql).toString();

		Query queryPage = this.getSession().createQuery(sql.toString());
		for (int i = 0; i < params.size(); i++) {
			queryPage.setParameter(i, params.get(i));
		}
		queryPage.setFirstResult((pageNum - 1) * pageSize);
		queryPage.setMaxResults(pageSize);
		List<DeliveryManVO> deliveryManList = queryPage.list();
		map.put("list", deliveryManList);
		return map;
	}
}
